package me.baicai.weixin.provider.utils;

import me.baicai.modules.belldandy.gcb.domain.FinancePayroll;
import me.baicai.utils.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class ExcelUtils
{
    public static List<FinancePayroll> readFinance(MultipartFile file)
    {
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);

        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<FinancePayroll> list = new ArrayList<FinancePayroll>();

        if (workbook != null)
        {
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++)
            {
                //获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (sheet == null)
                {
                    continue;
                }
                //获得当前sheet的开始行
                int firstRowNum = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                //循环除了所有行,如果要循环除第一行以外的就firstRowNum+1

                Row tem = sheet.getRow(0);
                String targe = tem.getCell(0).toString().substring(0, 7).replaceAll("年", "-");

                for (int rowNum = firstRowNum + 4; rowNum <= lastRowNum; rowNum++)
                {
                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if (row == null || row.getFirstCellNum() != 0 ||
                        StringUtils.isEmpty(row.getCell(0).toString()))
                    {
                        continue;
                    }
                    FinancePayroll financePayroll = new FinancePayroll();
                    financePayroll.setCreated(new Date());
                    financePayroll.setUpdated(new Date());
                    financePayroll.setTargetd(targe);
                    financePayroll.setName(getCellValue(row.getCell(1)));
                    financePayroll.setField1(getCellValue(row.getCell(2)));
                    financePayroll.setField2(getCellValue(row.getCell(3)));
                    financePayroll.setField3(getCellValue(row.getCell(4)));
                    financePayroll.setField4(getCellValue(row.getCell(5)));
                    financePayroll.setField5(getCellValue(row.getCell(7)));
                    financePayroll.setField6(getCellValue(row.getCell(8)));
                    financePayroll.setField7(getCellValue(row.getCell(9)));
                    financePayroll.setField8(getCellValue(row.getCell(10)));
                    financePayroll.setField9(getCellValue(row.getCell(11)));
                    financePayroll.setField10(getCellValue(row.getCell(12)));
                    financePayroll.setField11(getCellValue(row.getCell(13)));
                    financePayroll.setSum1(getCellValue(row.getCell(14)));
                    financePayroll.setField12(getCellValue(row.getCell(15)));
                    financePayroll.setField13(getCellValue(row.getCell(16)));
                    financePayroll.setField14(getCellValue(row.getCell(17)));
                    financePayroll.setSum2(getCellValue(row.getCell(18)));
                    financePayroll.setField15(getCellValue(row.getCell(19)));
                    financePayroll.setField16(getCellValue(row.getCell(20)));
                    financePayroll.setSum3(getCellValue(row.getCell(21)));
                    financePayroll.setField17(getCellValue(row.getCell(23)));
                    list.add(financePayroll);

                   /*
                    //获得当前行的开始列
                    int firstCellNum = row.getFirstCellNum();
                    //获得当前行的列数
                    int lastCellNum = row.getLastCellNum();
                    if (lastCellNum > 0)
                    {

                        //循环当前行
                        for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++)
                        {
                            Cell cell = row.getCell(cellNum);
                            cellValues.add(getCellValue(cell));
                        }

                    }*/
                }
            }
        }
        return list;
    }

    public static Workbook getWorkBook(MultipartFile file)
    {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try
        {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith("xls"))
            {
                //2003
                workbook = new HSSFWorkbook(is);
            }
            else if (fileName.endsWith("xlsx"))
            {
                //2007 及2007以上
                workbook = new XSSFWorkbook(is);
            }
        }
        catch (IOException e)
        {
            e.getMessage();
        }
        return workbook;
    }

    public static String getCellValue(Cell cell)
    {
        String cellValue = "0.00";
        if (cell == null || StringUtils.isEmpty(cell.toString()))
        {
            return cellValue;
        }
        //判断数据的类型
        //判断数据的类型
        switch (cell.getCellTypeEnum())
        {
            case NUMERIC: //数字
                cellValue = stringDateProcess(cell);
                break;
            case STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: //公式
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case BLANK: //空值
                cellValue = "";
                break;
            case ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    public static String stringDateProcess(Cell cell)
    {
        String result = new String();
        if (HSSFDateUtil.isCellDateFormatted(cell))
        {// 处理日期格式、时间格式
            SimpleDateFormat sdf = null;
            if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm"))
            {
                sdf = new SimpleDateFormat("HH:mm");
            }
            else
            {// 日期
                sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            }
            Date date = cell.getDateCellValue();
            result = sdf.format(date);
        }
        else if (cell.getCellStyle().getDataFormat() == 58)
        {
            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            double value = cell.getNumericCellValue();
            Date date = DateUtil
                .getJavaDate(value);
            result = sdf.format(date);
        }
        else
        {
            double value = cell.getNumericCellValue();
            CellStyle style = cell.getCellStyle();
            DecimalFormat format = new DecimalFormat();
            String temp = style.getDataFormatString();
            // 单元格设置成常规
            if (temp.equals("General"))
            {
                format.applyPattern("#");
            }
            result = value+"";
        }

        return result;
    }
}
